package Dao;

import model.Goods;
import org.apache.commons.dbutils.*;
import org.apache.commons.dbutils.handlers.*;
import utils.DataSourceUtils;

import java.sql.SQLException;
import java.util.*;

public class GoodsDao {


    public List<Goods> GetAllProduct() throws SQLException {
        QueryRunner r=new QueryRunner(DataSourceUtils.getDataSource());
        String sql="select * from product where is_removed=0";
    //        try {
    //            Connection conn = DataSourceUtils.getDataSource().getConnection();
    //            System.out.println("conn对象：" + conn);
    //            System.out.println("当前连接到的数据库是：" + conn.getCatalog());
    //        } catch (Exception e) {
    //            e.printStackTrace();
    //            System.out.println("获取数据库连接时发生异常！");
    //        }
        return r.query(sql,new BeanListHandler<Goods>(Goods.class));
    }
    public List<Goods> GetProductByUserId(int userId) throws SQLException {
        QueryRunner r=new QueryRunner(DataSourceUtils.getDataSource());
        String sql="select * from product where user_id=?";
        //        try {
        //            Connection conn = DataSourceUtils.getDataSource().getConnection();
        //            System.out.println("conn对象：" + conn);
        //            System.out.println("当前连接到的数据库是：" + conn.getCatalog());
        //        } catch (Exception e) {
        //            e.printStackTrace();
        //            System.out.println("获取数据库连接时发生异常！");
        //        }
        return r.query(sql,new BeanListHandler<Goods>(Goods.class),userId);
    }
    public List<Goods> GetNotRemovedProductByUserId(int userId, boolean is_removed) throws SQLException {
        QueryRunner r=new QueryRunner(DataSourceUtils.getDataSource());
        String sql="select * from product where user_id=? and is_removed=?";
        //        try {
        //            Connection conn = DataSourceUtils.getDataSource().getConnection();
        //            System.out.println("conn对象：" + conn);
        //            System.out.println("当前连接到的数据库是：" + conn.getCatalog());
        //        } catch (Exception e) {
        //            e.printStackTrace();
        //            System.out.println("获取数据库连接时发生异常！");
        //        }
        return r.query(sql,new BeanListHandler<Goods>(Goods.class),userId,is_removed);
    }
    public List<Goods> GetIsRemovedProductByUserId(int userId, boolean is_removed) throws SQLException {
        QueryRunner r=new QueryRunner(DataSourceUtils.getDataSource());
        String sql="select * from product where user_id=? and is_removed=?";
        //        try {
        //            Connection conn = DataSourceUtils.getDataSource().getConnection();
        //            System.out.println("conn对象：" + conn);
        //            System.out.println("当前连接到的数据库是：" + conn.getCatalog());
        //        } catch (Exception e) {
        //            e.printStackTrace();
        //            System.out.println("获取数据库连接时发生异常！");
        //        }
        return r.query(sql,new BeanListHandler<Goods>(Goods.class),userId,is_removed);
    }
    public List<Goods> selectGoodsByTypeID(int typeID) throws SQLException {
        if(typeID==0)
        {
            String sql="select * from product";
            QueryRunner r=new QueryRunner(DataSourceUtils.getDataSource());
            return  r.query(sql,new BeanListHandler<Goods>(Goods.class));
        }
        else
        {
            String sql="select * from product where category_id=?";
            QueryRunner r=new QueryRunner(DataSourceUtils.getDataSource());
            return  r.query(sql,new BeanListHandler<Goods>(Goods.class),typeID);
        }
    }

    public List<Goods> findByIsRemoved(boolean isRemoved) throws SQLException {

        String sql = "SELECT * FROM product WHERE is_removed = ?";
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
        return runner.query(sql, new BeanListHandler<>(Goods.class), isRemoved);

//        return runner.query(sql, new BeanListHandler<>(Goods.class), isRemoved);
    }


    public int getUserIdById(int id) throws SQLException {
        QueryRunner r=new QueryRunner(DataSourceUtils.getDataSource());
        String sql="select user_id from product where id=?";
        return r.query(sql, new ScalarHandler<Integer>(), id);
    }
    public Goods getGoodsById(int id) throws SQLException {
        QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());
        String sql = "select p.id,p.name,p.detail,p.image1,p.image2,p.image3,p.old_level,p.user_id,p.inventory,p.price,p.is_bargain,p.create_time,p.is_removed,c.id category_id,c.name categoryname, u.avatar useravatar, u.nickname usernickname from product p,category c,user u where p.id = ? and p.category_id=c.id and p.user_id=u.id";
        return r.query(sql, new BeanHandler<Goods>(Goods.class),id);
    }
    public float getPriceById(int id) throws SQLException {
        QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());
        String sql = "select price from product where id=?";
        Number num = r.query(sql, new ScalarHandler<>(), id);
        return num == null ? 0f : num.floatValue();
    }

    public void insert(Goods g)throws SQLException{
        QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());
        //创建实例r，传入数据源DataSource
        String sql = "insert into product(name, detail, image1,image2,image3, old_level, category_id, user_id, inventory, price,  is_bargain, create_time, is_removed) values(?,?,?,?,?,?,?,?,?,?,?,?,?)";

//        try {
//            Connection conn = DataSourceUtils.getDataSource().getConnection();
//            System.out.println("conn对象：" + conn);
//            System.out.println("当前连接到的数据库是：" + conn.getCatalog());
//        } catch (Exception e) {
//            e.printStackTrace();
//            System.out.println("获取数据库连接时发生异常！");
//        }
        r.update(sql, g.getName(), g.getDetail(), g.getImage1(), g.getImage2(), g.getImage3(), g.getOld_level(), g.getCategory_id(),
                g.getUser_id(),g.getInventory(), g.getPrice(),g.isIs_bargain(), g.getCreate_time(),  g.isIs_removed());
    }

    public void update(Goods g)throws SQLException{
        QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());
        String sql = "update product set name=?, detail=?, image1=?, image2=?, image3=?, old_level=?, category_id=?, user_id=?, inventory=?, price=?, is_bargain=?, create_time=?, is_removed=? where id=?";
        r.update(sql, g.getName(), g.getDetail(), g.getImage1(),g.getImage2(),g.getImage3(), g.getOld_level(), g.getCategory_id(), g.getUser_id(),
                g.getInventory(), g.getPrice(), g.isIs_bargain(), g.getCreate_time(), g.isIs_removed(), g.getId());
    }

    public void delete(int id)throws SQLException{
        QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());
        String sql = "delete from product where id=?";
        r.update(sql, id);
    }
}

